﻿CREATE PROCEDURE [ServiceEngine].[ListSchemaIDsForEndpoint]
	@EndpointID uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	DISTINCT SO.SchemaID
	FROM	[ServiceEngine].SchemaObjects SO 
	JOIN	[ServiceEngine].MessageParts MP on MP.SchemaObjectID = SO.SchemaObjectID
	JOIN	[ServiceEngine].OperationVersions OV on OV.OperationVersionID = MP.OperationVersionID 	
	JOIN	[ServiceEngine].Endpoints_PublishedOperations EA on OV.OperationVersionID = EA.OperationVersionID
	WHERE	SO.DateRevised = NULL
	AND		EA.EndpointID = @EndpointID
	AND		OV.IsPublic = 1

	UNION ALL

	SELECT	DISTINCT SO.SchemaID
	FROM	[ServiceEngine].SchemaObjects SO 
	JOIN	[ServiceEngine].SoapFaults SF on SF.SchemaObjectID = SO.SchemaObjectID
	JOIN	[ServiceEngine].OperationVersions OV on OV.OperationVersionID = SF.OperationVersionID 	
	JOIN	[ServiceEngine].Endpoints_PublishedOperations EA on OV.OperationVersionID = EA.OperationVersionID
	WHERE	SO.DateRevised = NULL
	AND		EA.EndpointID = @EndpointID
	AND		OV.IsPublic = 1
END


